#!/usr/bin/env python
# encoding: utf-8
'''
@author: zhaowenpeng
@contact: winston@peipeiyun.com
@software: garner
@file: merge_excel_column.py
@time: 2020/7/28 4:43 下午
@desc:
'''

import pandas as pd

data = []
df = pd.read_excel('/Users/winston/workspace/chstong/data_scripts/data_export/hetian.xlsx')
columns=['code','oe','add_time',
'article_brandCode','article_no','brandCode'
'cars','cid','id','is_article_no','is_exist',
'match_produce_cars_count','origin_oe',
'parts_id','produce_cars_count',
'sale_cars_count','search_name','show_id',
'standard_name','upd_time',
'user_id','show_id']

df.drop(columns=['add_time','article_brandCode','article_no','cid','id','is_article_no','parts_id','upd_time','user_id'])
order = ['code','oe','origin_oe','brandCode','is_exist','sale_cars_count','produce_cars_count','match_produce_cars_count','search_name','standard_name','cars']
df = df[order]
df.loc[df['is_exist']=='TRUE','is_exist']='存在'
df.loc[df['is_exist']=='FALSE','is_exist']='不存在'

data.append(df)

print(df['is_exist'])
#mydf = df.set_index(["code"])

#不存在的OE
pd_not_exist=df[df.is_exist==False]
data.append(pd_not_exist)

#OE销售车型为空
pd_none_sales=df[df.sale_cars_count==0]
data.append(pd_none_sales)

#适用车型为空
pd_none_general_cars=df[(df.sale_cars_count==0)&(df.produce_cars_count==0)&(df.is_exist==True)]
data.append(pd_none_general_cars)

#标准名称为空
pd_none_standard_name=df[((df.sale_cars_count>0)|(df.produce_cars_count>0))&(df.standard_name.isnull())]
print(pd_none_standard_name.head())
data.append(pd_none_standard_name)

#匹配成功的数据
match_parts=df[(df.sale_cars_count>0)|(df.produce_cars_count>0)]
match_parts.set_index(['code', 'oe'], inplace=True)

sheet_names=['全部产品','不存在的OE','OE销售车型为空','OE无适用车型','标准名称为空']

columns={'code':'产品','oe':"OE",'origin_oe':'用户输入OE',
                      'brandCode':"品牌",'is_exist':'是否存在','sale_cars_count':'销售车型',
                      'produce_cars_count':'适用车型','match_produce_cars_count':'匹配适用车型',
                      'search_name':'搜索名称','standard_name':'标准名称','cars':'车型'}
for d in data:
    d.set_index(['code','oe'],inplace=True)
for d in data:
    d.rename(columns=columns, inplace=True)

match_parts.rename(columns=columns, inplace=True)
save_path = 'data/data01.xlsx'
writer = pd.ExcelWriter(save_path)

index=0
# for name in sheet_names:
#     print(name,index)
#     data[index].to_excel(writer,sheet_name=name)
#     index+=1
# print('witer')
# writer.save()
#
# mydf.to_excel("hetian.xlsx")

#匹配成功的产品数据

match_parts.to_excel("hetian_match.xlsx")